Sales Reporting

The Sales > Order Reporting pre-filter can be used to generate various sales reports. Numerous selection criteria allow users to filter and sort results for conducting detailed sales analysis. Once a report has been generated, users can drill down into specific sales orders to view additional information. In addition, users with the appropriate permissions may also modify selected orders.

Configuration

The configuration for Sales reports is fairly straightforward and generally involves three steps:

  1. Naming the user-defined Sales Detail and Sales Summary reports via the "Title" field on the Grid Layouts for these reports. (Note: In versions 16.0 and 16.01, the name of these reports is defined via System > Maintenance > Captions. Prior to version 16.0, the name of these reports is defined on the "Sales 2" tab in System > Options.)
  2. Adding any additional user-defined versions of existing sales reports. This is accomplished by first copying the Grid Layout of the existing sales report. See the Adding a new user report section for complete information. This option was introduced in version 16.02.
  3. Configuring the appropriate sales reports, including the above below mentioned user-defined reports, by modifying the report grid and adding, removing, or modifying the fields that will be displayed on the grid reports. Additional information on grid layouts is available via Configuring Grid Layouts and Automated Reports.*

Notes

  • If users are satisfied with the standard system reports, no additional configuration is required.
  • Version 16.02 also introduced Grid Layout Security which expands the security used to control which Users or User Groups have access to Grid Layouts/reports in the system.

Process

Generating a sales report

To generate a sales report:

  1. Navigate to Sales > Order Reporting.
  2. Complete the pre-filter as necessary.
  3. Click the "View" button to display the results.
  4. Users may double click the column headings to sort the results as desired.
  5. It is possible to create a new Sales Order or modify an existing one without leaving the Order Reporting pre-filter. To create a new Sales Order, click on the "New" button. To modify an existing Sales Order, enter a Sales Order number and click "Modify".
  6. To view the General Ledger postings for a given Sales Order, click on the Sales Order in the grid and click on the "View Postings" button.
  7. To drill down and see the details of a given Sales Order, click on the Sales Order in the grid and click "View Detail".

Generating a Trend or Year Over Year analysis

Year over year analysis can be accomplished by using the "Trend - Dollars" and "Trend - Units" report types in the Order Reporting pre-filter. Once either of these reports is selected, the "Backorder" field is changed to "Years" to allow users to specify the number of years that will be used in the analysis. Specific details on how the reports are displayed and can be used is highlighted below:

  • "Backorders" field is replaced by Years when trend report is selected. This field defaults to 1 (which is non-year over year).
  • When Years <= 1, the report has no Year column and the fiscal year is shown in the column name.
  • When Years > 1, the report has a Year column and the column headers for the buckets just show the period name. Note that "Show Zero Items" must be selected for years with no activity to display.
  • A Total column is displayed at the end of the report, for comparing total of one year to total of another year.
  • The start/end date can be used to compare just parts of a year to years past.

Determining sales profits

Calculating the variance between an item's cost to produce and its quoted price on a Sales Order ensures that the material is being produced at a reasonable cost and sold at the proper price point. Proactively maintaining accurate production costs, i.e. before sending a Quote to a customer, ensures that the company is only spending the necessary amount to produce the material while receiving the maximum profit. To calculate the profit margin on orders, generate a Sales > Order Reporting "Cost Summary" report for orders with a status of "Shipped" and other pre-filters as desired. This report displays the profit, margin, and standard versus actual variances by order and is especially helpful in pinpointing orders that have a loss or large variances. For lines with negative profits or large variances, it may be a good idea to review and update the Part's standard costs in the system and/or re-negotiate and update selling prices with Bill-to Companies. As far as digging into production costs, refer to the Production Reporting page.

FAQ & Diagnostic Tips

Why am I unable to add product details, such as Part Number, to the grid?

Product details are only available to the Order Detail and Sales User Detail 1-5 reports. To see a list of the fields available to add to the grid layout, click on the "Show Fields" button in the toolbar of the report results screen.

Last month I modified the Order Reporting grid to get the report I needed. This month the report is not the same. What is the problem?

If the "Grid For" field was set to All users at the time the grid was modified, it is possible another user modified the grid layout. When modifying a grid to suit a user's individual needs, save the new layout by selecting Specific User in the "Grid For" field on the Edit Grid Layout form. Click on the search box for the "User" field and select the appropriate username. Click "Save". An alternative is to create a user-defined report by following the directions above.

My report is based on payment terms (Page 2 tab > "Terms" field). How do I add the payment terms description to the report results grid?

Create a user-defined column on the grid with a nested IIF statment. For example, (IIF(bi_teid=1, "NET 30", IIF(bi_teid=2, "NET 60", "FIX Calculation"))) Navigate to Accounting > Maintenance > Terms to determine the correct bi_teid values and the exact descriptions. In the above example, "Fix Calculation" is displayed if a new payment term is added to the system but the expression for this column is not updated. If bi_teid is not shown in the report results, click on the "View All Fields" button in the "Edit Grid Layouts" toolbar to determine whether it is available to the current report.

How do I add sales tax information to the report results grid?

Follow the directions for modifying the report grid to add these columns to your report: to_statax (Tax Region 1), to_loctax (Tax Region 2) and pr_taxable.

Am I able to view parts reserved to Sales Orders via the Sales > Order Reporting pre-filter?

No. To view a report of reserved parts, navigate to Inventory > Inventory Reporting > Report type = Lots, View = Reserved > Click "View". Double click on the Post ref column to group the lots by the Sales Order number.

How do I generate a report that shows sales by state?

Find a report type that displays the Ship-to State in the grid. Then, click on the "Subtotal Report" button in the report results screen toolbar to summarize by state. If such a report does not exist, modify the grid of an existing report by adding the "Ship-to State" field (sh_state).

Tip: When modifying a grid in Sales > Order Reporting, note that the "sm_fname" field will display the word Multiple and the "sm_lname" field will display the word Salesman, if multiple salesmen are listed on the Sales Assignment tab on the Sales Order form. If one of these Salesmen is marked Primary, that salesmen’s name will appear in the "sm_fname" and "sm_lname" fields.

I am no longer able to see a Sales Order in Sales > Order Reporting. What happened?

Something is missing from the Sales Order. Go to Sales > Order Reporting. Enter the Sales Order on the pre-filter and click "Modify". In each field with a selection box, re-select the values in the fields. Save the order. This should allow you to view the Sales Order in reports.

Can DEACOM track returns vs sales?

For stocked parts, returns can be tracked against sales by running an inventory transactions report on the item and time period in question. The ‘Action’ column will show Shipped order and the ‘Out’ column shows sales and the ‘In’ column shows returns (assuming returns are logged as negative sales orders). For non-stocked parts, an Order Detail report can be run with a filter on the part that is being tracked.

Another option is to set up the customer inventory property in the item master. It allows you to track inventory with the Sales Customer Inventory report. Serialization can also be enabled on these parts to provide a further element of tracking.

Do sales Ranking reports include sales taxes in the total dollar calculation?

No. Ranking reports do not include the sales tax from orders in the total dollars calculations. This is due to the fact that the ranking in these reports is often done based on part number, category, sub-category, and the item search 1-5 fields and therefore exclude sales tax lines since they do not have an item number on them. In situations where companies wish to validate order information on ranking reports, the best report to use is the Order Detail report, filtered not to include tax lines. One option for removing sales tax lines is to use an advanced filter of Sales Order Lines fields and set the condition to remove Part Number IDs (or_prid) not equal to 0 and then run the report.

Tip: The "salesrep" field may be added to the Customer Number searchbox via System > Maintenance > Searchboxes. Once added to the searchbox it will be available when using the searchbox in the "Cust. Part Number" field on the "Edit Sales Order Line" form.

Tip: In situations where Freight Purchase Orders are generated from Sales Orders, the following fields are copied to the purchase order to support freight and profit reporting as detailed below.

In addition, when generating a Freight PO via the Freight PO button the following information/fields are copied to the purchase order.

  • The pr_codenum (part number) field from the SO line is copied to the pu_user1 (PO user defined field) field so the system will know remember the part number that the charge is for.
  • The to_ordnum (sales order number) field from the sales order header is copied to the pu_ordnum (sales order number listed on PO line) field so the system will know the sales order that the charge is for.
  • The bi_id (bill-to company ID) field from the sales order header to the pu_biid (bill-to company listed on the PO line) field so the system will know the customer that the charge is for)
  • The system will now also ensure that the resulting PO is for the amount that was entered in the original form (vendor/cost).  If not, the system will adjust the largest line on the order so that it is. These fields that are copied are useful for variance reporting outside of the General Ledger.  The pr_codenum - pu_user1 linkage can be used to compare or_frtcost to pu_cost on an item by item basis.  To_ordnum - pu_ordnum linkage can be used to compare the freight bill to the revenue collected.  Pu_biid can be used to show the freight bill in the SO Profit Summary report.